package sv.gob.mh.dgii.jdbc;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.beanutils.BasicDynaBean;
import org.apache.commons.beanutils.BasicDynaClass;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.beanutils.RowSetDynaClass;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import sv.gob.mh.dgii.common.Constants;
import sv.gob.mh.dgii.common.Utils;
import sv.gob.mh.dgii.xml.XmlResultSet;

/**
 * @author TPAR
 * <b>Created by </b>agonzalez
 * <b>for</b>jsiit-utils
 * <b>on</b> Nov 16, 2006 1:51:51 PM
 * <b>Purpose</b> 
 * <p>
 * </p>
 */
/**
 * @author TPAR <b>Created by </b>agonzalez <b>for</b>jsiit-utils <b>on</b> Nov
 *         16, 2006 1:53:05 PM <b>Purpose</b>
 *         <p>
 *         Manejar interfaces con JDBC al momento de extrair informacion para
 *         los reportes
 *         </p>
 */
public class JdbcHelper {

	Log log = LogFactory.getLog(JdbcHelper.class);

	private Connection conn;

	/**
	 * @param conn
	 */
	public JdbcHelper(Connection conn) {
		if (conn == null) {
			throw new NullPointerException();
		}
		this.conn = conn;
	}

	/**
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private PreparedStatement getPreparedStatement(String sql, Object[] params)
			throws SQLException {
		PreparedStatement pstmt = conn.prepareStatement(sql);
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				Object param = params[i];
				int type = resolveType(param);

				pstmt.setObject(i + 1, param, type);
			}
		}
		return pstmt;
	}

	public PreparedStatement getPreparedStatement(String sql)
			throws SQLException {
		return conn.prepareStatement(sql);
	}

	public void addToBatch(PreparedStatement pstmt, Object[] params)
			throws SQLException {
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				Object param = params[i];
				int type = resolveType(param);
				if (!Utils.isNullOrEmpty(param)) {
					log.debug("param " + param.getClass() + "type" + type);
				}

				pstmt.setObject(i + 1, param, type);
			}// for
		}// if
		pstmt.addBatch();
	}

	/**
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public RowSetDynaClass getRowSet(String sql, Object[] params)
			throws SQLException {
		PreparedStatement pstmt = getPreparedStatement(Constants.SELECT_ALL
				+ sql + Constants.WHERE_MAX_ALL, params);
		ResultSet rs = pstmt.executeQuery();
		RowSetDynaClass rowSet = new RowSetDynaClass(rs);
		rs.close();
		pstmt.close();
		return rowSet;
	}

	public XmlResultSet getXmlResultSet(String sql, Object[] params)
			throws SQLException {
		log.debug("XmlResultSet sql: -->" + Constants.SELECT_ALL + sql
				+ Constants.WHERE_MAX_ALL);
		log.debug("XmlResultSet params: -->" + params);
		PreparedStatement pstmt = getPreparedStatement(Constants.SELECT_ALL
				+ sql + Constants.WHERE_MAX_ALL, params);
		ResultSet rs = pstmt.executeQuery();
		XmlResultSet xmlRs = new XmlResultSet(rs);
		rs.close();
		pstmt.close();
		return xmlRs;
	}

	public List<DynaBean> getListDynaBean(String sql, Object[] params) {
		log.debug("getListDynaBean sql: -->" + Constants.SELECT_ALL + sql
				+ Constants.WHERE_MAX_ALL);
		log.debug("getListDynaBean params: -->" + params);
		PreparedStatement pstmt;
		List<DynaBean> list;
		try {
			pstmt = getPreparedStatement(Constants.SELECT_ALL + sql
					+ Constants.WHERE_MAX_ALL, params);
			list = getRows(pstmt);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	private List<DynaBean> getRows(PreparedStatement nps) {
		try {
			ResultSet rs = nps.executeQuery();
			Oracle10gRowSetDynaClass rsdc = new Oracle10gRowSetDynaClass(rs);

			List<DynaBean> temp = new ArrayList<DynaBean>();

			BasicDynaClass bdc = new BasicDynaClass("row", BasicDynaBean.class,
					rsdc.getDynaProperties());

			for (Iterator<DynaBean> rows = rsdc.getRows().iterator(); rows
					.hasNext();) {
				DynaBean oldRow = rows.next();
				DynaBean newRow = bdc.newInstance();
				PropertyUtils.copyProperties(newRow, oldRow);
				temp.add(newRow);
			}
			nps.close();
			return temp;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	@SuppressWarnings("unchecked")
	public static List<DynaBean> getRows(NamedParameterStatement nps) {
		try {
			ResultSet rs = nps.executeQuery();
			Oracle10gRowSetDynaClass rsdc = new Oracle10gRowSetDynaClass(rs);

			List<DynaBean> temp = new ArrayList<DynaBean>();

			BasicDynaClass bdc = new BasicDynaClass("row", BasicDynaBean.class,
					rsdc.getDynaProperties());

			for (Iterator<DynaBean> rows = rsdc.getRows().iterator(); rows
					.hasNext();) {
				DynaBean oldRow = rows.next();
				DynaBean newRow = bdc.newInstance();
				PropertyUtils.copyProperties(newRow, oldRow);
				temp.add(newRow);
			}

			nps.close();
			return temp;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}


	@SuppressWarnings("unchecked")
	private int resolveType(Object param) {
		int sqlType = Types.VARCHAR;

		if (param == null) {
			sqlType = Types.NULL;
		} else {
			Class paramClass = param.getClass();
			if (param instanceof String) {
				sqlType = Types.VARCHAR;
			} else if (paramClass.equals(double.class)
					|| param instanceof Double) {
				sqlType = Types.DOUBLE;
			} else if (param instanceof BigDecimal) {
				sqlType = Types.NUMERIC;
			} else if (param instanceof java.sql.Date) {
				sqlType = Types.DATE;
			} else if (paramClass.equals(int.class) || param instanceof Integer) {
				sqlType = Types.INTEGER;
			} else if (paramClass.equals(long.class) || param instanceof Long) {
				sqlType = Types.BIGINT;
			} else if (paramClass.equals(float.class) || param instanceof Float) {
				sqlType = Types.REAL;

			}
		}
		return sqlType;
	}

	public Object executeFunction(String sql, Object[] params)
			throws SQLException {
		CallableStatement cstmt = null;
		Object outPut;
		try {
			cstmt = this.conn.prepareCall(sql);

			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					Object param = params[i];
					if (i == 0) {
						cstmt.registerOutParameter(1, resolveType(param));
					} else {
						log.debug("parametro -->" + param + " tipo-->"
								+ resolveType(param));
						cstmt.setObject(i + 1, param, resolveType(param));
					}
				}
			}
			log.debug("begin executing ...>" + sql + "<");
			cstmt.execute();
			log.debug("end executing ...>" + sql + "<");
			outPut = cstmt.getObject(1);
		} finally {
			cstmt.close();
			conn.close();
			cstmt = null;
			conn = null;

		}

		return outPut;
	}

	/**
	 * @see
	 * @param cadena
	 *            sql y arreglo de parametros
	 * @return nothing,
	 */
	public void executeProcedure(String sql, Object[] params)
			throws SQLException {
		CallableStatement cstmt = null;
		try {
			cstmt = conn.prepareCall(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					Object param = params[i];
					cstmt.setObject(i + 1, param, resolveType(param));
					
				}
			}
			cstmt.execute();

		} finally {
			cstmt.close();
			conn.close();

			cstmt = null;
			conn = null;
		}

	}

	public static String createInString(String str[]) {
		String temp = "";

		for (String item : str) {
			temp += "'" + item + "', ";
		}
		int i = temp.lastIndexOf(",");
		temp = temp.substring(0, i);
		return temp;
	}

	public static String createInString(List<String> strs) {
		String temp = "";

		for (String item : strs) {
			temp += "'" + item + "', ";
		}
		int i = temp.lastIndexOf(",");
		temp = temp.substring(0, i);
		return temp;
	}

}// class